/* DataSynProcess.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   tools	2008-8-26	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.apep.gw.synch;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import client.XMLSend;

import com.apep.connect.APEPRequest;
import com.apep.connect.APEPResponse;
import com.apep.connect.APEPServiceException;
import com.apep.connect.processor.ConnectProcessor;
import com.apep.framework2008.APEPException;
import com.apep.gw.service.StuationAnalyse;
import com.apep.gw.service.impl.ProductStuationAnalyse;
import com.apep.lib.xml.SampleElement;
import com.hitrust.gs.bean.GSHandleRecord;
import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.bean.GSProductBidBean;
import com.hitrust.gs.bean.GSProductTypeBean;
import com.hitrust.gs.business.GSCommunicationApepProcessor;
import com.hitrust.gs.business.GSCommunicationGwcsPro;
import com.hitrust.gs.business.GSManufactureProcessor;
import com.hitrust.gs.dao.GSHandleRecordDAO;
import com.hitrust.gs.dao.GSProductBidDao;
import com.hitrust.gs.dao.GSProductTypeDao;
import com.hitrust.gs.lib.GSAutoAuditongDateConfigurator;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSManufactureVO;

import framework.core.Config;
import framework.core.ConstDefine;
import framework.core.DBConnection;
import framework.core.DBEngine;
import framework.core.DBPreparedStatement;
import framework.core.DBResult;
import framework.core.LogWriter;

public class DataSynProcess extends ConnectProcessor {
    private static Map tableColumnNames = new HashMap();
    private GSCommunicationApepProcessor commPro = new GSCommunicationApepProcessor();//与apep交互
    private GSCommunicationGwcsPro gwcspro = new GSCommunicationGwcsPro();

    /*
     * (non-Javadoc)
     * 
     * @see com.apep.connect.processor.ConnectProcessor#process(com.apep.connect.APEPRequest)
     */
    public APEPResponse process(APEPRequest aRequest) throws APEPServiceException {
        APEPResponse tResponse = null;
        String errorInfo = "产品不存在,无法与荣爱同步,请同步产品.";
        boolean rs = true;
        String tAction = aRequest.getAction();
        GSLogWriter.debug("接收请求：" + aRequest.toXml());
        if ("upload".equals(tAction)) {
            // 1、接受并处理客户端上传的数据
            try {
                tResponse = processUpload(aRequest);
                List list = aRequest.getDataElementList("data");
                SampleElement se = (SampleElement) list.get(0);
                String tableName = se.getChildElement("tablename").getTagValue();
                if (tableName.equals("t_out_head") || tableName.equals("t_out_sub")) {
                    if (XMLSend.getInstance().isToRong()) {
                        String actionName = "SubmitOutWh";
                        String dataName = "OutWh";
                        // 传输xml to 荣爱
                        rs = XMLSend.getInstance().submitOutWhSendXML(actionName, dataName, se);
                        if (!rs) {
                            tResponse.set("ResultCode", errorInfo);
                            return tResponse;
                        }
                    }
                }
                if (tableName.equals("t_in_head") || tableName.equals("t_in_sub")) {
                    if (XMLSend.getInstance().isToRong()) {
                        String actionName = "SubmitInWh";
                        String dataName = "InWh";
                        // 传输xml to 荣爱
                        rs = XMLSend.getInstance().submitInWhSendXML(actionName, dataName, se);
                        if (!rs) {
                            tResponse.set("ResultCode", errorInfo);
                            return tResponse;
                        }

                    }
                }
            } catch (APEPException e) {
                e.printStackTrace();
            } catch (GSException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } else if ("download".equals(tAction)) {
            // 3、接受并处理客户端的数据下载请求
            tResponse = processDownlod(aRequest);
        } else if ("QueryReserve".equalsIgnoreCase(tAction)) {//库存查询
            tResponse = commPro.queryWarehouseApep(aRequest);
        } else if ("QueryProduct".equalsIgnoreCase(tAction)) {//供应产品查询
            tResponse = commPro.queryProductApep(aRequest);      
         } else if ("ExprotReserve".equalsIgnoreCase(tAction)) {//库存查询下载
            tResponse = commPro.queryWarehouseApepToExcel(aRequest);
        } else if ("ExprotProduct".equalsIgnoreCase(tAction)) {//供应产品查询下载
            tResponse = commPro.queryProductsApepToExcel(aRequest);
        } else if ("SendOrder".equalsIgnoreCase(tAction)) {//送货确认
            tResponse = commPro.processSendOrder(aRequest);
        } else if ("RefuseOrder".equalsIgnoreCase(tAction)) {//订单拒绝
            tResponse = commPro.processRefuseOrder(aRequest);
        } else if ("FinishOrder".equalsIgnoreCase(tAction)) {//交货确认
            tResponse = commPro.processFinishOrder(aRequest);
        } else if ("DisposeCancelOrder".equalsIgnoreCase(tAction)) {//处理取消订单
            tResponse = commPro.processDisposeCancelOrder(aRequest);
        } else if ("DeposeOrder".equalsIgnoreCase(tAction)) {//处理作废订单
            tResponse = commPro.processDeposeOrder(aRequest);
        } else if ("ApplyProduct".equalsIgnoreCase(tAction)) {//接收补货申请
            tResponse = commPro.processApplyProduct(aRequest);
        } else if ("QueryWarehousing".equals(tAction)) {//出入库查询
            tResponse = commPro.queryWarehousing(aRequest);
        } else if ("ExprotWarehousing".equals(tAction)) {//出入库查询下载
            tResponse = commPro.queryWarehousingApepToExcel(aRequest);
        } else if ("GetOrderDetail".equalsIgnoreCase(tAction)) {//接收cs请求送货单号得订单
            tResponse = gwcspro.getOrderDetailGwcs(aRequest);
        }else if("QueryOrderDetail".equalsIgnoreCase(tAction)){//接收CS请求查询订单明细
        	tResponse = gwcspro.queryOrderDetail(aRequest);
        }else if ("UploadtoGwbs".equals(tAction)) {//处理中心库发送的数据
            tResponse = processCenterWarehouse(aRequest);
        }else if ("DownloadFromGwbs".equals(tAction)) {
            tResponse = processGwbsDownlod(aRequest);//从GWBS下载数据
        }else if ("SignOrder".equalsIgnoreCase(tAction)) {//cs请求发送签收信息
            tResponse = gwcspro.processFinishOrder(aRequest);
        } else if ("testAction".equalsIgnoreCase(tAction)) {//测试
            tResponse = new APEPResponse();
            tResponse.set("ResultCode", APEPResponse.RESULT_SUCCESS);
            tResponse.set("ErrorDesc", "");
        } else {
            // 4、不支持的请求
            throw new APEPServiceException(APEPServiceException.APEP_EXC_CODE_12, APEPServiceException.APEP_EXC_MSG_1201, "不支持的交易请求类型(" + aRequest.getAction()
                    + ")");
        }
        if (tResponse == null) {
            throw new APEPServiceException(APEPServiceException.APEP_EXC_CODE_12, APEPServiceException.APEP_EXC_MSG_1201, "不支持的交易请求类型(" + aRequest.getAction()
                    + ")");
        }
        return tResponse;
    }

    /**
     * 数据上传处理过程
     * 
     * @param aRequest
     *            交易请求对象
     * @throws APEPServiceException 
     * @returns 交易结果对象
     */
    private String getName(APEPRequest req) throws APEPServiceException {
        List list = req.getDataElementList("data");
        SampleElement se = (SampleElement) list.get(0);
        String tableName = se.getChildElement("tablename").getTagValue();
        return tableName;
    }

    private APEPResponse processUpload(APEPRequest req) throws APEPException {
        APEPResponse res = new APEPResponse();
        try {
            List list = req.getDataElementList("data");
            SampleElement se = (SampleElement) list.get(0);
            String tableName = se.getChildElement("tablename").getTagValue();
            //判断是否自动审核临时产品
            GSAutoAuditongDateConfigurator.getInstance();
            if (tableName.equalsIgnoreCase("t_product") && se.getChildElement("is_normal").getTagValue().equals("0")
                    && se.getChildElement("status").getTagValue().equals("0")
                    && GSBaseConstants.AUTO_AUDITING.equals("true") && GSAutoAuditongDateConfigurator.isAutoAuditing()) {
                se.deleteChildElement("status");
                se.deleteChildElement("auditing_status");
                se.addChildElement(new SampleElement("status", "1"));
                se.addChildElement(new SampleElement("auditing_status", "2"));

            }
            if(tableName.equalsIgnoreCase("t_product")&& (se.getChildElement("status").getTagValue().equals("0")||se.getChildElement("is_buy").getTagValue().equals("0"))){
            	 GSProductTypeDao dao = new GSProductTypeDao();
            	  GSProductTypeBean bean = dao.findProductTypeByProuductCode(se.getChildElement("concentricity_code").getTagValue().trim());
                  if(bean !=null){
                	  dao.updatePrudctTypeStatusByProductCode(se.getChildElement("concentricity_code").getTagValue().trim());
                  }
                  GSProductBidDao bidDao = new GSProductBidDao();
                  GSProductBidBean bidBean = bidDao.findProductBidByProuductCode(se.getChildElement("concentricity_code").getTagValue().trim());
                  if(bidBean !=null){
                	  bidDao.updatePrudctBidStatusByProductCode(se.getChildElement("concentricity_code").getTagValue().trim());
                  }
            }


            String[] columns = null;
            try {
                columns = (String[]) tableColumnNames.get(tableName);
                if (columns == null || columns.length == 0) {
                    columns = getColumnNames(tableName);
                    tableColumnNames.put(tableName, columns);
                }
            } catch (RuntimeException e) {
                LogWriter.error(e.getLocalizedMessage(), e);
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99);
                res.set("ErrorDesc", "服务器不存在对应的表");
                return res;
            }

            String id = se.getChildElement(columns[0]).getTagValue();

            // 这里需要一个map(或者其他)来提取对应的类来进行数据封装
            String sql = "";
            if (updateOrInsert(tableName, id)) {
            	if(tableName.toLowerCase().equals("t_product")){
	            	GSHandleRecordDAO dao = new GSHandleRecordDAO();
	            	GSHandleRecord vo = new GSHandleRecord();
	    			vo.setActionInfo("upload");
	    			vo.setHandleTime(DateUtil.getCurrentDate("yyyy-MM-dd HH:mm:ss"));
	    			vo.setHandleClass("");
	    			vo.setRemark("表名:"+tableName);
	    			vo.setUserName("");
	    			vo.setUserId(id);
	    			vo.setUserIP("");
	    			dao.insert(vo);
            	}
                sql = updateSql(se, columns, tableName);
            } else {
                sql = insertSql(se, columns, tableName);
            }
            sql = sql.replaceAll("null", "");
            if (doSqlDesc(sql)) {
                res.set("ResultCode", APEPResponse.RESULT_SUCCESS); // 交易结果 - 成功返回
                res.set("ErrorDesc", "");
            } else {
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99); // 交易结果 - 成功返回
                res.set("ErrorDesc", "服务器执行脚本失败(SQL脚本执行后影响的条数为0)");
            }
        } catch (Exception e) {
            // 异常时进行处理

            // 如果异常为应用异常，则设置对应的错误码，否则设置错误码为系统异常99
            if (e instanceof APEPServiceException) {
                APEPServiceException tAPEPException = (APEPServiceException) e;
                res.set("ResultCode", tAPEPException.getCode());
                res.set("ErrorDesc", e.getMessage());
            } else {
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99);
                res.set("ErrorDesc", e.getMessage());
                LogWriter.warn("ConnectProcessorV1::processCreateOrder() - " + e.getLocalizedMessage());
            }
        } finally {
            // TODO:清理及释放代码
        }
        return res;
    }
    
    
    /**
     * 处理中心库发送的数据
     * 
     */
    private APEPResponse processCenterWarehouse(APEPRequest req) {
        APEPResponse res = new APEPResponse();
        StuationAnalyse productStuationAnalyse = new ProductStuationAnalyse();
        try {
            List list = req.getDataElementList("data");
            SampleElement se = (SampleElement) list.get(0);
    		GSManufactureProcessor processor = new GSManufactureProcessor();
    		GSManufactureBean bean=processor.getManufInfoByConcentricityId(se.getChildElement("concentricityCode").getTagValue().trim());
    		GSManufactureVO vo = new GSManufactureVO();
			vo.setGs_manufacture_id(se.getChildElement("apepCode").getTagValue().trim());
			vo.setGs_concentricity_id(se.getChildElement("concentricityCode").getTagValue().trim());
			vo.setGs_manufacture_name(se.getChildElement("name").getTagValue().trim());
			vo.setGs_manufacture_category_id(se.getChildElement("categoryCode").getTagValue().trim());
			vo.setGs_manufacture_brand(se.getChildElement("productBrand").getTagValue()==null?"":se.getChildElement("productBrand").getTagValue().trim());
			vo.setGs_manufacture_type(se.getChildElement("model").getTagValue()==null?"":se.getChildElement("model").getTagValue().trim());
			vo.setGs_manufacture_spec(se.getChildElement("spec").getTagValue()==null?"":se.getChildElement("spec").getTagValue().trim());	
			vo.setGs_manufacture_unitprice(se.getChildElement("planPrice").getTagValue().trim());
			vo.setGs_quant_unit(se.getChildElement("productUnit").getTagValue().trim());
			vo.setGs_quality_assure_period(se.getChildElement("qualityAssurePeriod").getTagValue().trim());
			vo.setGs_manufacturer_name(se.getChildElement("manufaturer").getTagValue()==null?"":se.getChildElement("manufaturer").getTagValue().trim());
			vo.setGs_make_area(se.getChildElement("area").getTagValue()==null?"":se.getChildElement("area").getTagValue().trim());
			vo.setGs_provider_id(se.getChildElement("providerCode").getTagValue().trim());
			vo.setGs_adapt_machine_type(se.getChildElement("adaptMachineType").getTagValue()==null?"":se.getChildElement("adaptMachineType").getTagValue().trim());
			vo.setGs_recorder_id(se.getChildElement("recorderId").getTagValue().trim());
			vo.setGs_manufacture_record_date(se.getChildElement("recorderDate").getTagValue().trim());
			vo.setGs_is_tradeflat_manufacture(se.getChildElement("isTradeFlatProduct").getTagValue().trim());
			vo.setGs_appender_property(se.getChildElement("appendProperty").getTagValue()==null?"":se.getChildElement("appendProperty").getTagValue().trim());
			vo.setGs_remark(se.getChildElement("remark").getTagValue()==null?"":se.getChildElement("remark").getTagValue().trim());
			vo.setGsValidity(se.getChildElement("status").getTagValue().trim());
			vo.setGs_drawing_peice_code(se.getChildElement("drawingPieceCode").getTagValue()==null?"":se.getChildElement("drawingPieceCode").getTagValue().trim());
			vo.setIs_buy(se.getChildElement("isBuy").getTagValue().trim());
			vo.setGs_balance_mode(se.getChildElement("balanceMode").getTagValue().trim());
			vo.setIsupdate(se.getChildElement("isUpdate").getTagValue()==null?"":se.getChildElement("isUpdate").getTagValue().trim());
			vo.setIs_normal(se.getChildElement("isNormal").getTagValue().trim());
			vo.setCreate_user_id(se.getChildElement("createUserId").getTagValue().trim());
			vo.setCreate_user(se.getChildElement("createUser").getTagValue().trim());
			vo.setCreate_time(se.getChildElement("createTime").getTagValue().trim());
			vo.setLast_update_time(se.getChildElement("lastUpdateTime").getTagValue().trim());
			vo.setLast_update_user_id(se.getChildElement("lastUpdateUserId").getTagValue().trim());
			vo.setLast_update_user(se.getChildElement("lastUpdateUser").getTagValue().trim());
			vo.setBalance_customer_code("0");
			vo.setPic(" ");
			vo.setRecent_adjustprice_date(se.getChildElement("recentAdjustPriceDate").getTagValue().trim());
			vo.setRecent_adjusted_price(se.getChildElement("recentAdjustPrice").getTagValue().trim());
			vo.setGs_planprice(se.getChildElement("unitPrice").getTagValue().trim());
			vo.setWarehouse_code(se.getChildElement("warehouseCode").getTagValue().trim());
			vo.setWarehouse_name(se.getChildElement("warehouseName").getTagValue().trim());
			vo.setKm_quality_assure_period(se.getChildElement("kmQualityAssurePeriod").getTagValue()==null?"":se.getChildElement("kmQualityAssurePeriod").getTagValue().trim());
    		vo.setProductType(se.getChildElement("productType").getTagValue()==null?"":se.getChildElement("productType").getTagValue().trim());
    		vo.setBidType(se.getChildElement("isBid").getTagValue()==null?"":se.getChildElement("isBid").getTagValue().trim());
			if(bean==null){
    			processor.saveManufactureKM(vo);	
    			GSManufactureBean productBean = processor
				.getManufInfoByConcentricityId(vo
						.getGs_concentricity_id());
		if (productBean != null) {
			productStuationAnalyse.insert(
					processor.appendInsertContent(vo),
					vo.getCreate_time(),
					vo.getCreate_user_id(),
					vo.getCreate_user(),
					productBean.getId());
		}
		GSProductTypeDao dao = new GSProductTypeDao();
		GSProductBidDao bidDao = new GSProductBidDao();
		GSProductTypeBean typebean = dao
				.findProductTypeByProuductCodeNoStauts(vo
						.getGs_concentricity_id().trim());
		if (bean != null) {
			typebean.setStatus(Integer
					.parseInt(ConstDefine.STR_VALID_YES));
			typebean.setProductCode(vo.getGs_concentricity_id()
					.trim());
			typebean.setProviderCode(vo.getGs_provider_id());
			typebean.setProductType(Integer.parseInt(vo.getProductType()));

			typebean.setIsBid(Integer.parseInt(vo.getBidType()));
			typebean.setLastUpdateTime(new framework.core.DateUtil()
					.getFullString());
			typebean.setLastUpdateUser(vo.getLast_update_user());
			typebean.setLastUpdateUserId(vo.getLast_update_user_id());
			dao.update(typebean);
		
		} else {
			typebean = new GSProductTypeBean();
			typebean.setCreateTime(new framework.core.DateUtil().getFullString());
			typebean.setCreateUser(vo.getCreate_user());
			typebean.setCreateUserId(vo.getCreate_user_id());
			typebean.setIsBalance(1);
			
			typebean.setIsBid(Integer.parseInt(vo.getBidType()));

			typebean.setLastUpdateTime(new framework.core.DateUtil()
					.getFullString());
			typebean.setLastUpdateUser(vo.getLast_update_user());
			typebean.setLastUpdateUserId(vo.getLast_update_user_id());
			typebean.setProductCode(vo.getGs_concentricity_id()
					.trim());
			typebean.setProductType(Integer.parseInt(vo.getProductType()));
			typebean.setProviderCode(vo.getGs_provider_id());
			typebean.setProductId(productBean != null ? productBean
					.getId() : "");
			typebean.setRemark("");
			typebean.setStatus(Integer
					.parseInt(ConstDefine.STR_VALID_YES));
			dao.insert(typebean);
		}
//		if (productType.equals(GSBaseConstants.PRODUCT_TYPE1)) {
			GSProductBidBean bidBean = bidDao
					.findProductBidByProuductCodeNoStauts(vo
							.getGs_concentricity_id().trim());
			if (bidBean != null) {
				bidBean.setProductId(bean.getId());
				bidBean.setProductCode(vo
						.getGs_concentricity_id().trim());
				bidBean.setProviderCode(vo.getGs_provider_id());
				bidBean.setProductType(Integer.parseInt(vo.getBidType()));
				bidBean.setLastUpdateTime(new framework.core.DateUtil()
						.getFullString());
				bidBean.setLastUpdateUser(vo.getLast_update_user());
				bidBean.setLastUpdateUserId(vo.getLast_update_user_id());
				bidBean.setStatus(Integer
						.parseInt(ConstDefine.STR_VALID_YES));
				bidDao.update(bidBean);
			} else {
				bidBean = new GSProductBidBean();
				bidBean.setCreateTime(new framework.core.DateUtil()
						.getFullString());
				bidBean.setCreateUser(vo.getCreate_user());
				bidBean.setCreateUserId(vo.getCreate_user_id());
				bidBean.setProductName(vo
						.getGs_manufacture_name());
				bidBean.setLastUpdateTime(new framework.core.DateUtil()
						.getFullString());
				bidBean.setLastUpdateUser(vo.getLast_update_user());
				bidBean.setLastUpdateUserId(vo.getLast_update_user_id());
				bidBean.setProductCode(vo
						.getGs_concentricity_id().trim());
				bidBean.setProductType(Integer.parseInt(vo.getBidType()));
				bidBean.setProviderCode(vo.getGs_provider_id()
						.trim());
				bidBean.setProductId(productBean != null ? productBean
						.getId() : vo.getId());
				bidBean.setRemark("");
				bidBean.setStatus(Integer
						.parseInt(ConstDefine.STR_VALID_YES));
				bidDao.insert(bidBean);
			}
    		}
    	    res.set("ResultCode", "00");
            res.set("ErrorDesc", "");
            return res;
        } catch (Exception e) {
        	 // 如果异常为应用异常，则设置对应的错误码，否则设置错误码为系统异常99
            if (e instanceof APEPServiceException) {
                APEPServiceException tAPEPException = (APEPServiceException) e;
                res.set("ResultCode", tAPEPException.getCode());
                res.set("ErrorDesc", e.getMessage());
            } else {
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99);
                res.set("ErrorDesc", e.getMessage());
                LogWriter.warn("processCenterWarehouse() - " + e.getLocalizedMessage());
            }
        }
        return res;
    }
    
    
    /**
     *  从GWBS下载数据
     * 
     * @param aRequest
     *            交易请求对象
     * @returns 交易结果对象
     */
    private APEPResponse processGwbsDownlod(APEPRequest req) {
        APEPResponse res = new APEPResponse();
        //判断请求数据有效性
        try {
            //String name = req.getDataValueNoNull("Table_name");
            List list = req.getDataElementList("data");
            SampleElement se = (SampleElement) list.get(0);
            String name = se.getChildElement("Table_name").getTagValue();
            String lastTime = se.getChildElement("Last_download_time").getTagValue();
            String lastId = se.getChildElement("Last_Data_id").getTagValue();
            String upload = "";
   
            String sql = null;
              if(lastId.equals("id")){
            	  sql = "select * from " + name + " order by last_update_time asc";
                }else{
                  sql = "select * from " + name + " where last_update_time >= '" + lastTime +"' order by last_update_time asc";
               }  
            int once = 20;
            try {
                once = Integer.parseInt(Config.getOnce());
            } catch (Exception e) {

                e.printStackTrace();
                once = 200;
            }
            res = doSelect(sql, name, res, once);
            return res;
        } catch (APEPServiceException e) {

            e.printStackTrace();
            if (e instanceof APEPServiceException) {
                APEPServiceException tAPEPException = (APEPServiceException) e;
                res.set("ResultCode", tAPEPException.getCode());
                res.set("ErrorDesc", tAPEPException.getMessage() + " - " + tAPEPException.getDetailMessage());
                LogWriter.error("processGwbsDownlod - " + e.getLocalizedMessage());
            } else {
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99);
                res.set("ErrorDesc", APEPServiceException.APEP_EXC_MSG_9901);
                LogWriter.error("processGwbsDownlod - " + e.getLocalizedMessage());
            }
            return res;
        }
    }

    /**
     * 产品数据上传处理过程
     * 
     * @param aRequest
     *            交易请求对象
     * @returns 交易结果对象
     */
    private APEPResponse processDownlod(APEPRequest req) {
        APEPResponse res = new APEPResponse();
        //判断请求数据有效性
        try {
            //String name = req.getDataValueNoNull("Table_name");
            List list = req.getDataElementList("data");
            SampleElement se = (SampleElement) list.get(0);
            String name = se.getChildElement("Table_name").getTagValue();
            String lastTime = se.getChildElement("Last_download_time").getTagValue();
            String lastId = se.getChildElement("Last_Data_id").getTagValue();
            String warehouseCode = se.getChildElement("Warehouse_code").getTagValue();
            String upload = "";
            try {
				GSAutoAuditongDateConfigurator.getInstance();
				upload = GSAutoAuditongDateConfigurator.getUploadTime();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
            String sql = null;
            if (GSBaseConstants.AUDITING.equals("true")) {
            	String tenWc="";//判断是否是10位仓库编码,如处理新新
            	if(warehouseCode.trim().length()==10){
            		tenWc=" or warehouse_code = '"+warehouseCode.substring(0, warehouseCode.length()-4)+"' ";
            	}
                if (name.toLowerCase().equals("t_product")){
                	if(upload.equals("")){
                		sql = "select * from " + name + " where last_update_time >= '" + lastTime + "' and (is_normal = 1 or (is_normal = 0 and warehouse_code = '"
                        + warehouseCode + "' or warehouse_code = '"+warehouseCode.substring(0, warehouseCode.length()-2)+"' "+tenWc+" )) order by last_update_time asc";
               	}else{
                		sql = "select * from " + name + " where last_update_time >= '" + lastTime +"' and last_update_time <= '"+upload+ "' and (is_normal = 1 or (is_normal = 0 and warehouse_code = '"
                        + warehouseCode + "' or warehouse_code = '"+warehouseCode.substring(0, warehouseCode.length()-2)+"' "+tenWc+" )) order by last_update_time asc";
                	}
                }
            } else {
                if (name.toLowerCase().equals("t_product"))
                	if(upload.equals("")){
                		sql = "select * from " + name + " where last_update_time >= '" + lastTime + "' and is_normal = 1 order by last_update_time asc";
                	}else{
                		sql = "select * from " + name + " where last_update_time >= '" + lastTime +"' and last_update_time <= '"+upload+ "' and is_normal = 1 order by last_update_time asc";
                	}
                }

            if (name.toLowerCase().equals("t_provider"))
                sql = "select * from " + name + " where last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_product_category"))
                sql = "select * from " + name + " where last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if(name.toLowerCase().equals("t_product_temporary"))//替换表
                sql = "select * from " + name + " where last_update_time >= '" + lastTime + "'and warehouse_code like '"
                            + warehouseCode.substring(0, warehouseCode.length()-2) + "%' order by last_update_time asc";
            
            //申跃配件工作流，需下载主表和明细表
//            if(name.toLowerCase().equals("gs_order_detail")){
//                sql = "select * from " + name + " orderDetail left join GS_ORDER gsOrder on" +
//                		" orderDetail.GS_ORDER_TEMPID = gsOrder.GS_ORDER_TEMPID" +
//                		" where orderDetail.GS_LASTMODIFY_DATE >= '" + lastTime +"' and orderDetail.gs_provider_id = '90001' and " +
//                		" orderDetail.gs_order_detail_status = '10' " +
//                		"and gsOrder.GS_WORKCORP_ID ='"+warehouseCode+"' order by orderDetail.GS_LASTMODIFY_DATE asc";
//            }
//            if(name.toLowerCase().equals("gs_order")){
//                sql = "select * from " + name + " gsOrder left join GS_ORDER_DETAIL orderDetail on " +
//                		"orderDetail.GS_ORDER_TEMPID = gsOrder.GS_ORDER_TEMPID " +
//                		"where orderDetail.GS_LASTMODIFY_DATE >='" +lastTime+"' and orderDetail.gs_provider_id = '90001' and " +
//                		" orderDetail.gs_order_detail_status = '10' "+
//                		" and gsOrder.GS_WORKCORP_ID ='"+warehouseCode+"' order by orderDetail.GS_LASTMODIFY_DATE asc";
//            } 
                        //共享同一分公司下的数据
            if (name.toLowerCase().equals("t_warehouse"))
                sql = "select * from " + name + " where code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_position"))
                sql = "select * from " + name + " where warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_warehouse_category"))
                sql = "select * from " + name + " where warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_warehouse_category_relation"))
                sql = "select * from " + name + " where warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_warehouse_product"))
                sql = "select * from " + name + " where warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_warehouse_staff"))
                sql = "select * from " + name + " where  warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_customer_staff"))
                sql = "select * from " + name + " where   customer_code in (select code from t_customer where warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%') and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_customer"))
                sql = "select * from " + name + " where  warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            if (name.toLowerCase().equals("t_warehouse_customer"))
                sql = "select * from " + name + " where  warehouse_code like '"+warehouseCode.substring(0, warehouseCode.length()-2)+"%' and last_update_time >= '" + lastTime + "' order by last_update_time asc";
            
            //调拨下载
            if(name.toLowerCase().equals("t_account_head")){
            	sql = "select * from "+name+" where last_update_time > '"+lastTime+"' and (warehouse_code_to = '"+warehouseCode+"' or warehouse_code_from = '"+warehouseCode+"' )" +
            			" order by last_update_time asc";
            }
            if(name.toLowerCase().equals("t_account_sub")){
            	sql = "select sub.* from "+name+" sub,t_account_head head where sub.ACCOUNT_BILL_ID = head.ID "
            		+"and sub.LAST_UPDATE_TIME > '"+lastTime+"' and (head.WAREHOUSE_CODE_TO = '"+warehouseCode+"' or head.WAREHOUSE_CODE_FROM = '"+warehouseCode+"') order by sub.LAST_UPDATE_TIME";
            }     
            if(name.toLowerCase().equals("t_product_map_old")){
            	sql = "select * from " + name + " where last_update_time >= '" + lastTime + "'and warehouse_code = '"
                + warehouseCode + "' order by last_update_time asc";
            }
            //均摊产品对应表下载
            if (name.toLowerCase().equals("t_product_out_category"))
                sql = "select * from " + name + " where last_update_time >= '" + lastTime + "' order by last_update_time asc";
            int once = 20;
            try {
                once = Integer.parseInt(Config.getOnce());
            } catch (Exception e) {

                e.printStackTrace();
                once = 200;
            }
            res = doSelect(sql, name, res, once);
            return res;
        } catch (APEPServiceException e) {

            e.printStackTrace();
            if (e instanceof APEPServiceException) {
                APEPServiceException tAPEPException = (APEPServiceException) e;
                res.set("ResultCode", tAPEPException.getCode());
                res.set("ErrorDesc", tAPEPException.getMessage() + " - " + tAPEPException.getDetailMessage());
                LogWriter.error("processDownlod - " + e.getLocalizedMessage());
            } else {
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99);
                res.set("ErrorDesc", APEPServiceException.APEP_EXC_MSG_9901);
                LogWriter.error("processDownlod - " + e.getLocalizedMessage());
            }
            return res;
        }
    }

    /** 根据一个表名获得此表的列名称数组 */
    private static String[] getColumnNames(String tableName) {
        String sql = "select * from " + tableName + " where 1<>1";
        DBConnection con = null;
        try {
            con = DBConnection.open(false);
        } catch (Exception e1) {

            e1.printStackTrace();
        }
        DBResult dbrs = null;
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        try {
            dbrs = DBEngine.doSelect(con, sql);
            rs = dbrs.getRs();
            rsm = rs.getMetaData();
            int metaCount = rsm.getColumnCount();
            String[] metaData = new String[metaCount];
            for (int i = 0; i < metaCount; i++) {
            	String str = rsm.getColumnName(i + 1).toLowerCase();
            	if(isFiltrateField(tableName,str)){
            		continue;
            	}else{
            		metaData[i]=str;  
            	}
            }
            List list = new ArrayList();
            for (int i = 0; i < metaData.length; i++) {
				if(metaData[i]==null||metaData[i].trim().toString().equals("")){
					continue;
				}else{
					list.add(metaData[i]);
				}
			}
            String[] newArray = new String[list.size()];
            for (int i = 0; i < list.size(); i++) {
				newArray[i]=(String) list.get(i);
			}
            
            return newArray;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                rs.close();
                dbrs.clear();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public static boolean isFiltrateField(String tableName,String columnName) throws Exception{
        GSAutoAuditongDateConfigurator config = GSAutoAuditongDateConfigurator.getInstance();
        String filtrateFieldAll = config.getFiltrateFieldAll();
        String[] filtrateFieldAllArray = filtrateFieldAll.split(",");
        for (int i = 0; i < filtrateFieldAllArray.length; i++) {
			if(columnName.equals(filtrateFieldAllArray[i])){
				return true;
			}
		}
        String filtrateFieldTable = config.getFiltrateFieldTable();
        String[] filtrateTableName = filtrateFieldTable.split(",");
        for (int i = 0; i < filtrateTableName.length; i++) {
			String[] filtrate =  filtrateTableName[i].split(":");
			if(filtrate[0].equals(tableName)){
				String[] filtrateColumn = filtrate[1].split(".");
				for (int j = 0; j < filtrateColumn.length; j++) {
					if(filtrateColumn[i].equals(columnName)){
						return true;
					}
				}
			}
		}
		return false;

    }

    /** insert 情况下 创建sql */
    private static String insertSql(SampleElement se, String[] columns, String tableName) {
        int size = columns.length;
        String str = "insert into " + tableName + " ( ";
        if (size == 1) {
            if ("WAREHOUSE_CODE".equalsIgnoreCase(columns[0])&&GSBaseConstants.IS_CENTER_WAREHOUSE==null) {
                str += columns[0] + " ) values ('" + DataSynProcess.changeWarehouseCode(se.getChildElement(columns[0]).getTagValue()) + "')";
            } else{
                String tagValue = getSampleElementTagValue(se, columns,0);
                str += columns[0] + " ) values ('" + tagValue + "')";
            } 
        } else {
            // 完成:字段,字段,字段..........
            for (int i = 0; i < size; i++) {
                if (i == 0)
                    str += columns[i];
                else {
                    if (!"IS_UPDATE".equalsIgnoreCase(columns[i]) && !"OLD_APEP_CODE".equalsIgnoreCase(columns[i])) {
                        str += "," + columns[i];
                    }
                }
            }
            str += " ) ";
        }
        // 完成values部分 已过滤掉 size == 1条件
        str += " values (";
        for (int i = 0; i < size; i++) {
            if (i == 0)
                if ("WAREHOUSE_CODE".equalsIgnoreCase(columns[i])&&GSBaseConstants.IS_CENTER_WAREHOUSE==null) {
                    str += "'" + DataSynProcess.changeWarehouseCode(se.getChildElement(columns[i]).getTagValue()) + "'";
                } else
                    str += "'" + se.getChildElement(columns[i]).getTagValue() + "'";
            else if ("WAREHOUSE_CODE".equalsIgnoreCase(columns[i])&&GSBaseConstants.IS_CENTER_WAREHOUSE==null) {
                str += ",'" + DataSynProcess.changeWarehouseCode(se.getChildElement(columns[i]).getTagValue()) + "'";
            } else {
                if (!"IS_UPDATE".equalsIgnoreCase(columns[i]) && !"OLD_APEP_CODE".equalsIgnoreCase(columns[i])) {
                    String tagValue = getSampleElementTagValue(se, columns, i);                  
                    str += ",'" + tagValue + "'";
                }
            }
        }
        str += ")";
        return str;
    }

    /**
     * @param se
     * @param columns
     * @param i
     * @return
     */
    private static String getSampleElementTagValue(SampleElement se, String[] columns, int i) {
        SampleElement value = se.getChildElement(columns[i]);
        String tagValue ="";
        if(value!=null){
            tagValue = value.getTagValue();  
        }
        return tagValue;
    }


    /** update 情况下 创建sql "id" 小写! */
    private static String updateSql(SampleElement se, String[] columns, String tableName) {

        int size = columns.length;
        // update table set ?=?,?=? where ?=?
        String str = "update " + tableName + " set ";
        if (size == 1) {
            if ("WAREHOUSE_CODE".equalsIgnoreCase(columns[0])&&GSBaseConstants.IS_CENTER_WAREHOUSE==null) {
                str += columns[0] + " = '" + DataSynProcess.changeWarehouseCode(se.getChildElement(columns[0]).getTagValue()) + "' where id = '"
                        + se.getChildElement("id").getTagValue() + "'";
            } else
                str += columns[0] + " = '" + se.getChildElement(columns[0]).getTagValue() + "' where id = '" + se.getChildElement("id").getTagValue() + "'";
        } else {
            for (int i = 0; i < size; i++) {
                if (i == 0)
                    if ("WAREHOUSE_CODE".equalsIgnoreCase(columns[i])&&GSBaseConstants.IS_CENTER_WAREHOUSE==null) {
                        str += columns[i] + "='" + DataSynProcess.changeWarehouseCode(se.getChildElement(columns[i]).getTagValue()) + "'";
                    } else {
                        if (!"IS_UPDATE".equalsIgnoreCase(columns[i]) && !"OLD_APEP_CODE".equalsIgnoreCase(columns[i])) {
                            str += columns[i] + "='" + se.getChildElement(columns[i]).getTagValue() + "'";
                        }
                    }
                else if ("WAREHOUSE_CODE".equalsIgnoreCase(columns[i])&&GSBaseConstants.IS_CENTER_WAREHOUSE==null) {
                    str += "," + columns[i] + "='" + DataSynProcess.changeWarehouseCode(se.getChildElement(columns[i]).getTagValue()) + "'";
                } else {
                    if (!"IS_UPDATE".equalsIgnoreCase(columns[i]) && !"OLD_APEP_CODE".equalsIgnoreCase(columns[i])) {
                        String tagValue = getSampleElementTagValue(se, columns, i); 
                        str += "," + columns[i] + "='" + tagValue + "'";
                    }
                }
            }
        }
        str += " where id='" + se.getChildElement("id").getTagValue() + "'";
        return str;
    }

    /** 执行update / insert 语句 返回true/false结果*/
    private static boolean doSql(String sql) {
        DBConnection con = null;
        int result = 0;
        try {
            con = DBConnection.open(false);
        } catch (Exception e1) {

            e1.printStackTrace();
        }
        DBPreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sql);
            result = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null)
                    pst.close();
            } catch (Exception e) {

                e.printStackTrace();
            }
            try {
                if (con != null)
                    con.close();
            } catch (Exception e) {

                e.printStackTrace();
            }
        }
        if (result > 0)
            return true;
        else
            return false;
    }
    
    /** 执行update / insert 语句 返回true/false结果*/
    private static boolean doSqlDesc(String sql) throws Exception {
        DBConnection con = null;
        DBPreparedStatement pst = null;
        int result = 0;
        
        try {
        	 con = DBConnection.open(false);
        	 pst = con.prepareStatement(sql);
             result = pst.executeUpdate();
             if(result > 0) {
            	 return true;
             } else {
            	 return false;
             }
        } finally {
        	if(pst != null) {
        		pst.close();
        	}
        	if(con != null && !con.isClosed()) {
        		con.close();
        	}
        }
    }

    /** true:update false:insert*/
    private static boolean updateOrInsert(String tableName, String id) {
        String str = "select * from " + tableName + " where id = '" + id + "'";
        DBConnection con = null;
        try {
            con = DBConnection.open(false);
        } catch (Exception e1) {

            e1.printStackTrace();
            return false;
        }
        DBResult dbrs = null;
        ResultSet rs = null;
        try {
            dbrs = DBEngine.doSelect(con, str);
            rs = dbrs.getRs();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (Exception e) {

            e.printStackTrace();
            return false;
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {

                e.printStackTrace();
            }
            try {
                if (dbrs != null)
                    dbrs.clear();
            } catch (Exception e) {

                e.printStackTrace();
            }
            try {
                if (con != null)
                    con.close();
            } catch (Exception e) {

                e.printStackTrace();
            }
        }
    }

    private static APEPResponse doSelect(String sql, String name, APEPResponse res, int once) {
        DBConnection con = null;
        try {
            con = DBConnection.open(false);
        } catch (Exception e1) {

            e1.printStackTrace();
        }
        ResultSet rs = null;
        ResultSetMetaData rsm = null;
        DBPreparedStatement pst = null;
        try {
            pst = con.prepareStatement(sql);
            rs = pst.executeQuery();
            rsm = rs.getMetaData();
            int metaCount = rsm.getColumnCount();
            String[] metaData = new String[metaCount];
            for (int i = 0; i < metaCount; i++) {
                metaData[i] = rsm.getColumnName(i + 1).toLowerCase();
            }
            int count = 0;
            while (rs.next()) {
                SampleElement sample = new SampleElement(name);
                for (int i = 0; i < metaData.length; i++) {
                    String value = rs.getString(metaData[i]);
                    if (value == null || "null".equals(value.toLowerCase()))
                        value = "";

                    boolean addFlag = true;
                    if ("t_product".equalsIgnoreCase(name)) {
                        if ("OLD_APEP_CODE".equalsIgnoreCase(metaData[i]) || "IS_UPDATE".equalsIgnoreCase(metaData[i])) {
                            addFlag = false;
                        }
                    }
                    if (addFlag)
                        sample.addChildElement(new SampleElement(metaData[i], value));
                }
                res.setData(sample);

                if (++count == once)
                    break;
            }
            if (count < once) {
                //所有结束
                res.set("ResultCode", "finish");
                res.set("ErrorDesc", "");
            } else {
                //本次结束但仍有数据
                res.set("ResultCode", APEPResponse.RESULT_SUCCESS);
                res.set("ErrorDesc", "");
            }
        } catch (Exception e) {
            e.printStackTrace();//异常时进行处理
            if (e instanceof APEPServiceException) {
                APEPServiceException tAPEPException = (APEPServiceException) e;
                res.set("ResultCode", tAPEPException.getCode());
                res.set("ErrorDesc", tAPEPException.getMessage() + " - " + tAPEPException.getDetailMessage());
            } else {
                res.set("ResultCode", APEPServiceException.APEP_EXC_CODE_99);
                res.set("ErrorDesc", APEPServiceException.APEP_EXC_MSG_9901);
                LogWriter.warn("processDownlod - " + e.getLocalizedMessage());
            }
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {

                e.printStackTrace();
            }
            try {
                if (pst != null)
                    pst.close();
            } catch (Exception e) {

                e.printStackTrace();
            }
            try {
                if (con != null)
                    con.close();
            } catch (Exception e) {

                e.printStackTrace();
            }
        }
        return res;
    }

    public static String changeWarehouseCode(String v) {
        if (v == null)
            v = "";
        String code = v;

        if ("000000".equals(code)) { //总库
            code = "00";
        } else if ("000001".equals(code)) { //中心仓库(重南)
            code = "00010000";
        } else if ("000101".equals(code)) { //一汽仓库-国江
            code = "00000200";
        } else if ("000201".equals(code)) { //二电仓库
            code = "0000040100";
        } else if ("000301".equals(code)) { //宝杨路仓库
            code = "00000600";
        } else if ("000401".equals(code)) { //一电武宁仓库
            code = "0000040000";
        } else if ("000402".equals(code)) { //一电云岭仓库
            code = "0000040001";
        } else if ("000500".equals(code)) { //巴士新新
            code = "000001";
        } else if ("000501".equals(code)) { //新新南陈路仓库
            code = "00000102";
        } else if ("000502".equals(code)) { //新新重南仓库
            code = "00000100";
        } else if ("000503".equals(code)) { //新新漕宝仓库
            code = "00000101";
        } else if ("000901".equals(code)) { //金山仓库
            code = "00010100";
        } else if ("00A000".equalsIgnoreCase(code)) { //巴士四汽
            code = "000003";
        } else if ("00B000".equalsIgnoreCase(code)) { //浦东巴士
            code = "000005";
        } else if ("00C000".equalsIgnoreCase(code)) { //上南巴士
            code = "000007";
        }

        return code;
    }
}
